package dao;

import entity.DeviceBean;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeviceDao {

    /**
     * 获取总页数
     * @param name 姓名,worknumber 工号,department 部门,worktime 入职时间,profession 专业,curpage 当前页码
     * @return List<TaskBean>--符合条件的任务列表
     *
     */
    public int findPageCount(String name, String number, String type
            , String vendor, String date, int page_size){
        String countSql="select count(*) from device where 1=1 ";
        String sql="";
        if (!name.isEmpty()) {
            sql += " AND name LIKE '%" + name + "%'";
        }
        if (!number.isEmpty()) {
            sql += " AND number LIKE '%" + number + "%'";
        }
        if (!type.isEmpty()) {
            sql += " AND type = '" + type + "'";
        }
        if (!vendor.isEmpty()) {
            sql += " AND vendor LIKE '%" + vendor + "%'";
        }
        if (!date.isEmpty()) {
            sql += " AND date = '" + date + "'";
        }
        countSql+=sql;

        int totalCount = 0;
        Connection connection=null;
        PreparedStatement pstmt=null;
        ResultSet resultSet=null;
        try{
            connection = DBUtil.getConnection();
            pstmt = connection.prepareStatement(countSql);
            resultSet = pstmt.executeQuery();
            if(resultSet.next()){
                totalCount = resultSet.getInt(1);
            }
        }catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(connection);
        }
        return ((totalCount+page_size)-1)/page_size;
    }
    /**
     * 查询满足条件的设备信息列表
     * @param name 设备名称
     * @param number 设备编号
     * @param type 设备类型
     * @param vendor 供应商名称
     * @param date 出厂日期
     * @return List<deviceBean>符合条件的设备信息列表
     */
    public List<DeviceBean> listFindDevice(String name, String number, String type
            , String vendor, String date, int curpage, int page_size) {
        List<DeviceBean> deviceList = new ArrayList<DeviceBean>();
        String sql = "SELECT * FROM device WHERE 1=1";
        if (!name.isEmpty()) {
            sql += " AND name LIKE '%" + name + "%'";
        }
        if (!number.isEmpty()) {
            sql += " AND number LIKE '%" + number + "%'";
        }
        if (!type.isEmpty()) {
            sql += " AND type = '" + type + "'";
        }
        if (!vendor.isEmpty()) {
            sql += " AND vendor LIKE '%" + vendor + "%'";
        }
        if (!date.isEmpty()) {
            sql += " AND date = '" + date + "'";
        }
        int start = curpage * page_size - page_size;
        String pge = " LIMIT " + start + ", " + page_size;  // 注意前面添加了一个空格,没这个空格就会报错
        sql = sql + pge;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            // 访问数据
            while (rs.next()) {
                DeviceBean obj = new DeviceBean();
                obj.setId(rs.getInt("id"));
                obj.setName(rs.getString("name"));
                obj.setNumber(rs.getString("number"));
                obj.setType(rs.getString("type"));
                obj.setVendor(rs.getString("vendor"));
                obj.setDate(rs.getDate("date").toString());
                obj.setTime(rs.getString("time"));
                obj.setAfterSales(rs.getString("afterSales"));
                obj.setPhone(rs.getString("phone"));
                obj.setIllustrate(rs.getString("illustrate"));
                deviceList.add(obj);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return deviceList;
    }

    public DeviceBean editFindDevice(String id) {
        DeviceBean obj = new DeviceBean();
        String sql = "select * from device where id=" + id;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rs.next();
            // 访问数据
            obj.setId(rs.getInt("id"));
            obj.setName(rs.getString("name"));
            obj.setNumber(rs.getString("number"));
            obj.setType(rs.getString("type"));
            obj.setVendor(rs.getString("vendor"));
            obj.setDate(rs.getDate("date").toString());
            obj.setTime(rs.getString("time"));
            obj.setAfterSales(rs.getString("afterSales"));
            obj.setPhone(rs.getString("phone"));
            obj.setIllustrate(rs.getString("illustrate"));
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return obj;
    }

    public void editDoUpdateDevice(DeviceBean device) {
        String sql = "update device set name=?, number=?, type=?, vendor=?, date=?" +
                ", time=?, afterSales=?, phone=?, illustrate=? where id=" + device.getId();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, device.getName());
            pstmt.setString(2, device.getNumber());
            pstmt.setString(3, device.getType());
            pstmt.setString(4, device.getVendor());
            pstmt.setString(5, device.getDate().toString());
            pstmt.setString(6, device.getTime());
            pstmt.setString(7, device.getAfterSales());
            pstmt.setString(8, device.getPhone());
            pstmt.setString(9, device.getIllustrate());
            int rowsUpdated = pstmt.executeUpdate();
            if (rowsUpdated > 0) {

            } else {

            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
    }

    public void addDoInsertDevice(DeviceBean device) {
        String sql = "insert into device (name, number, type, vendor, date, time, afterSales, phone, illustrate) " +
                "values(?,?,?,?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, device.getName());
            pstmt.setString(2, device.getNumber());
            pstmt.setString(3, device.getType());
            pstmt.setString(4, device.getVendor());
            pstmt.setString(5, device.getDate().toString());
            pstmt.setString(6, device.getTime());
            pstmt.setString(7, device.getAfterSales());
            pstmt.setString(8, device.getPhone());
            pstmt.setString(9, device.getIllustrate());
            int rowsUpdated = pstmt.executeUpdate();
            if (rowsUpdated > 0) {

            } else {

            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
    }

    public int deleteDevice(String id) {
        String sql = "delete from device where id=" + id;
        Connection conn = null;
        PreparedStatement pstmt = null;
        int res = -1;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            res = pstmt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return res;
    }
}
